package Work;

import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/*
1.创建数据库名为：cm
2.创建一张表：comment
字段：id  int 主键 自动递增
      name varchar
      comment varchar
3.往这张表添加几组数据

4.使用GOKJDBCUtil.java工具类对数据库中表进行增删改查
 */
public class Demo1 {
    public static void main(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/cm?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("1234");

        Connection connection = dataSource.getConnection();
        PreparedStatement p = null;
//        //1、创建数据库名为 cm
        String create_data = "create database cm";
        p = connection.prepareStatement(create_data);
        p.executeUpdate();

        //2.创建一张表：comment

        String create_table = "create table comment (id int primary key auto_increment," +
                                                        "name varchar(20)," +
                                                        "comment varchar(20))";
        p = connection.prepareStatement(create_table);
        p.executeUpdate();

        //3.往这张表添加几组数据
        String insert1 = "insert into comment values (1,\"张三\",\"学生\")";
        String insert2 = "insert into comment values (3,\"李四\",\"学生\")";
        String insert3 = "insert into comment values (2,\"王五\",\"学生\")";
        p = connection.prepareStatement(insert1);
        p.executeUpdate();
        p = connection.prepareStatement(insert2);
        p.executeUpdate();
        p = connection.prepareStatement(insert3);
        p.executeUpdate();

        //4.使用GOKJDBCUtil.java工具类对数据库中表进行增删改查
        //1、增
        String insert = "insert into comment values (5,\"赵六\",\"学生\")";
        p = connection.prepareStatement(insert);
        p.executeUpdate();

        //2、删
        String delete = "delete from comment where name = '张三'";
        p = connection.prepareStatement(delete);
        p.executeUpdate();

        //3、改
        String update = "update test set comment = '好学生' where name = '李四'";

        //4、查
        String select = "select * from comment";
        p = connection.prepareCall(select);
        ResultSet resultSet = p.executeQuery();

        while (resultSet.next()) {
            System.out.print(resultSet.getInt("id")+" ");
            System.out.print(resultSet.getString("name")+" ");
            System.out.println(resultSet.getString("comment"));
        }
    }
}


